const db = require('@/db/db-connection')
const path = require('path')
const callPythonScript = require('@/utils/runPythonScript')
const { multipleColumnSet, optionalMultipleColumnSet, newRandomId, dateFormat } = require('@/utils/common.util')

class ArticleModel {
  constructor () {
    this.tableName = 'article'
  }

  /**
   * search articles
   * @param {*} param
   */
  async find (param, sessionId) {
    try {
      const { pageNo = 1, pageSize = 10, sort, isFocus, ...object } = param

      const { columnSet, values } = multipleColumnSet(object, ' AND ')

      let focusSql = ''

      // 查询关注的人的文章
      if (isFocus) {
        focusSql = `WHERE ${this.tableName}.userId IN (SELECT ufu.focusId FROM user_focus_user ufu WHERE ufu.userId = '${sessionId}')`
      } else {
        focusSql = 'WHERE 1 = 1'
      }
      const orderBySql = `ORDER BY ${object['article.userId'] ? 'topTimeStap DESC, ' : ''}${sort === 'new' ? 'createTime' : 'viewCounts'} DESC`

      let sql = `SELECT ${this.tableName}.*, user.nickname, user.avatar, user.job, user.exp,

        (SELECT uaa.userId FROM user_agree_article AS uaa WHERE uaa.articleId = ${this.tableName}.id AND uaa.userId = ?) AS isLiker,

        (SELECT ufu.focusId FROM user_focus_user AS ufu WHERE ufu.userId = ? AND ufu.focusId = ${this.tableName}.userId) AS isFocusAuthor,

        (SELECT COUNT(*) FROM article_comment AS ac WHERE ac.articleId = ${this.tableName}.id) AS commentCounts,

        (SELECT COUNT(*) FROM user_focus_user AS ufu WHERE ufu.userId = ${this.tableName}.userId) AS foucsCounts,

        (SELECT COUNT(*) FROM user_focus_user AS ufu WHERE ufu.focusId = ${this.tableName}.userId) AS fansCounts

        FROM ${this.tableName}
        
        LEFT JOIN user ON user.id = ${this.tableName}.userId ${focusSql}`

      let totalSql = `SELECT COUNT(*) as total FROM ${this.tableName} ${focusSql}`

      if (!columnSet) {
        sql += ` ${orderBySql} LIMIT ${(pageNo - 1) * pageSize}, ${pageSize}`
      } else {
        sql += ` AND ${columnSet} ${orderBySql} LIMIT ${(pageNo - 1) * pageSize}, ${pageSize}`

        totalSql += ` AND ${columnSet}`
      }
      const [list, total] = [await db.query(sql, [sessionId, sessionId, ...values]), await db.query(totalSql, values)]

      list.forEach(arc => {
        arc.content = undefined

        arc.html = undefined

        arc.isFocusAuthor = Boolean(arc.isFocusAuthor)

        arc.isLiker = Boolean(arc.isLiker)
      })

      return {
        list,

        total: total[0].total,

        pageNo: Number(pageNo)
      }
    } catch (error) {
      throw new Error(error)
    }
  }

  /**
   * search articleInfo by id
   * @param {*} param
   * @param {*} sessionId
   */
  async findOne (articleId, sessionId, hideContent) {
    try {
      const sql = `SELECT ${this.tableName}.*, user.avatar, user.description AS userDescription, user.nickname, user.exp AS userExp,
        
        (SELECT COUNT(*) FROM article_comment AS ac WHERE ac.articleId = ${this.tableName}.id) AS commentCounts,
        
        (SELECT uca.userId FROM user_collect_article AS uca WHERE uca.articleId = ${this.tableName}.id AND uca.userId = ? ) AS isFlower,

        (SELECT uaa.userId FROM user_agree_article AS uaa WHERE uaa.articleId = ${this.tableName}.id AND uaa.userId = ? ) AS isLiker
        
        FROM ${this.tableName} LEFT JOIN user ON ${this.tableName}.userId = user.id
        
        WHERE ${this.tableName}.id = ?`

      const result = (await db.query(sql, [sessionId, sessionId, articleId]))[0]

      if (!result) {
        return null
      }

      return {
        ...result,

        content: hideContent ? undefined : result.content,

        isFlower: Boolean(result.isFlower),

        isLiker: Boolean(result.isLiker)
      }
    } catch (error) {
      throw new Error(error)
    }
  }

  /**
   * @param {*} param
   * @returns
   */
  async exists (param) {
    try {
      const { columnSet, values } = multipleColumnSet(param, ' AND ')

      const sql = `SELECT * FROM ${this.tableName} WHERE ${columnSet}`

      const result = await db.query(sql, values)

      return result[0]
    } catch (error) {
      throw new Error(error)
    }
  }

  /**
   * create article
   * @param {*} param0
   * @param {*} userId
   */
  async create ({ articleTitle, content, html, category, tag, coverImage, description, linkUrl, codeStyle, theme }, userId) {
    try {
      const id = newRandomId()

      const createTime = dateFormat(new Date())

      const sql = `INSERT INTO ${this.tableName} (userId, id, articleTitle, content, status, html, category, tag, coverImage, description, linkUrl, codeStyle, theme, createTime) 

        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`

      await db.query(sql, [userId, id, articleTitle, content, '1', html, category, tag, coverImage, description, linkUrl, codeStyle, theme, createTime])

      return id
    } catch (error) {
      throw new Error(error)
    }
  }

  /**
   * update article
   * @param {*} param0
   * @param {*} id
   */
  async update ({ articleTitle, content, html, category, tag, coverImage, description, linkUrl, codeStyle, theme }, id) {
    try {
      const updateTime = dateFormat(new Date())

      const { columnSet, values } = multipleColumnSet({

        articleTitle, content, html, category, tag, description, codeStyle, theme, updateTime

      })

      const { optionalColumnSet, optionalValues } = optionalMultipleColumnSet({

        coverImage, linkUrl

      })

      const sql = `UPDATE ${this.tableName} SET ${columnSet}, ${optionalColumnSet} WHERE id = ?`

      await db.query(sql, [...values, ...optionalValues, id])
    } catch (error) {
      throw new Error(error)
    }
  }

  async setTop (articleId, topTimeStap) {
    try {
      const sql = `UPDATE ${this.tableName} SET topTimeStap = ? WHERE id = ?`

      await db.query(sql, [topTimeStap, articleId])
    } catch (error) {
      throw new Error(error)
    }
  }

  /**
   * delete article
   * @param {*} id
   */
  async delete (id) {
    try {
      const sql = `DELETE ${this.tableName}, article_comment, user_collect_article, user_agree_article, user_agree_comment 

        FROM ${this.tableName} LEFT JOIN article_comment ON article_comment.articleId = ${this.tableName}.id

        LEFT JOIN user_collect_article ON user_collect_article.articleId = ${this.tableName}.id

        LEFT JOIN user_agree_article ON user_agree_article.articleId = ${this.tableName}.id

        LEFT JOIN user_agree_comment ON user_agree_comment.commentId = article_comment.id

        WHERE ${this.tableName}.id = ?`

      await db.query(sql, [id])
    } catch (error) {
      throw new Error(error)
    }
  }

  /**
   * auto increment fields
   * @param {*} id
   * @param {*} fields
   */
  async autoIncre (id, fields) {
    try {
      const sql = `UPDATE ${this.tableName} SET ${fields} = ${fields} + 1 WHERE id = ?`

      await db.query(sql, [id])
    } catch (error) {
      throw new Error(error)
    }
  }

  /**
   * auto decrement fields
   * @param {*} id
   * @param {*} fields
   */
  async autoDec (id, fields) {
    try {
      const sql = `UPDATE ${this.tableName} SET ${fields} = ${fields} - 1 WHERE id = ?`

      await db.query(sql, [id])
    } catch (error) {
      throw new Error(error)
    }
  }

  /**
   * get user collect articles
   * @param {*} userId
   * @returns
   */
  async findUserCollectArticle (userId, sessionId) {
    try {
      const sql = `SELECT ${this.tableName}.*, user.nickname, user.avatar,

        (SELECT COUNT(*) FROM article_comment as ac WHERE ac.articleId = ${this.tableName}.id) AS commentCounts,

        (SELECT uaa.userId FROM user_agree_article AS uaa WHERE uaa.articleId = ${this.tableName}.id AND uaa.userId = ?) AS isLiker

        FROM ${this.tableName} LEFT JOIN user ON user.id = ${this.tableName}.userId
        
        WHERE ${this.tableName}.id in (SELECT uaa.articleId FROM user_collect_article AS uaa WHERE uaa.userId = ?)`

      const list = await db.query(sql, [sessionId, userId])

      list.forEach(arc => {
        arc.content = undefined

        arc.isLiker = Boolean(arc.isLiker)
      })

      return list
    } catch (error) {
      throw new Error(error)
    }
  }

  /**
   * get user agree articles
   * @param {*} userId
   * @returns
   */
  async findUserAgreeArticle (userId, sessionId) {
    try {
      const sql = `SELECT ${this.tableName}.*, user.nickname, user.avatar,

        (SELECT COUNT(*) FROM article_comment as ac WHERE ac.articleId = ${this.tableName}.id) AS commentCounts,

        (SELECT uaa.userId FROM user_agree_article AS uaa WHERE uaa.articleId = ${this.tableName}.id AND uaa.userId = ?) AS isLiker
        
        FROM ${this.tableName} LEFT JOIN user ON user.id = ${this.tableName}.userId

        WHERE ${this.tableName}.id in (SELECT uaa.articleId FROM user_agree_article AS uaa WHERE uaa.userId = ?)`

      const list = await db.query(sql, [sessionId, userId])

      list.forEach(arc => {
        arc.content = undefined

        arc.isLiker = Boolean(arc.isLiker)
      })

      return list
    } catch (error) {
      throw new Error(error)
    }
  }

  /**
   * search article by kwywords
   * @param {*} pageNo
   * @param {*} pageSize
   * @param {*} value
   * @returns
   */
  async vagueFind ({ pageNo, pageSize, value }) {
    try {
      const values = value.length < 2 ? [value] : await callPythonScript(path.join(__dirname, '../python/nodejieba.py'), [value])

      const likeSql = values.reduce((prev, cur) => {
        cur = cur.replace(/%/g, '\\%').replace(/_/g, '\\_')
        if (!prev) {
          prev += `arc.articleTitle LIKE '%${cur}%' OR arc.description LIKE '%${cur}%'`
        } else {
          prev += `OR arc.articleTitle LIKE '%${cur}%' OR arc.description LIKE '%${cur}%'`
        }
        return prev
      }, '')

      const sql = `SELECT arc.id, arc.userId, arc.articleTitle, arc.description, arc.viewCounts, arc.likeCounts, arc.coverImage,
      
        (SELECT user.nickname FROM user WHERE arc.userId = user.id) AS authorNickname,

        (SELECT COUNT(*) FROM article_comment AS ac WHERE arc.id = ac.articleId) AS commentCounts
        
        FROM ${this.tableName} AS arc 
        
        WHERE arc.status = '2' AND (${likeSql}) LIMIT ${(pageNo - 1) * pageSize}, ${pageSize}`

      const totalSql = `SELECT COUNT(*) AS total FROM ${this.tableName} AS arc
        
        WHERE arc.status = '2' and (${likeSql})`

      const [list, total] = [await db.query(sql), await db.query(totalSql)]

      return {
        list,

        total: total[0].total,

        pageNo: Number(pageNo),

        args: {
          keywords: values
        }
      }
    } catch (error) {
      throw new Error(error)
    }
  }

  /**
   * find user recommend articles by tags
   * @param {*} tags
   * @param {*} userId
   */
  async findRecommend (tags, userId, filterArticleId) {
    try {
      const likeSql = tags.reduce((prev, _cur) => {
        if (!prev) {
          prev += 't.tag LIKE ?'
        } else {
          prev += ' OR t.tag LIKE ?'
        }

        return prev
      }, '')

      const sql = `SELECT t.id, t.articleTitle, t.tag, t.description, t.createTime, t.category, t.likeCounts, t.viewCounts, t.userId, user.nickname, user.avatar, user.job,

      (SELECT uaa.userId FROM user_agree_article AS uaa WHERE uaa.articleId = t.id AND uaa.userId = ?) AS isLiker,

      (SELECT ufu.focusId FROM user_focus_user AS ufu WHERE ufu.userId = ? AND ufu.focusId = t.userId) AS isFocusAuthor,

      (SELECT COUNT(*) FROM article_comment AS ac WHERE ac.articleId = t.id) AS commentCounts,

      (SELECT COUNT(*) FROM user_focus_user AS ufu WHERE ufu.userId = t.userId) AS foucsCounts

      FROM ${this.tableName} t 

      LEFT JOIN user ON user.id = t.userId

      WHERE t.status = '2' AND (${likeSql}) AND t.id != ? LIMIT 0, 20`

      const result = await db.query(sql, [userId, userId, ...tags.map(o => `%${o.replace(/%/g, '\\%').replace(/_/g, '\\_')}%`), filterArticleId])

      result.forEach(arc => {
        arc.isFocusAuthor = Boolean(arc.isFocusAuthor)

        arc.isLiker = Boolean(arc.isLiker)
      })

      return result
    } catch (error) {
      throw new Error(error)
    }
  }

  async findAuthorRecommend (userId, articleId) {
    const sql = `SELECT a.id, a.articleTitle, a.viewCounts, a.likeCounts FROM article a 
    
    WHERE a.status = '2' AND a.userId = ? AND a.id != ? ORDER BY a.viewCounts desc LIMIT 0, 5`

    try {
      const data = await db.query(sql, [userId, articleId])

      return data
    } catch (error) {
      throw new Error(error)
    }
  }

  /**
   * get article total
   * @param {*} param
   */
  async total (param) {
    try {
      const { columnSet, values } = multipleColumnSet(param, ' AND ')

      const sql = `SELECT COUNT(*) AS total FROM ${this.tableName} WHERE ${columnSet}`

      const total = await db.query(sql, values)

      return total[0].total
    } catch (error) {
      throw new Error(error)
    }
  }

  /**
   * get user article all viewCount total
   * @param { String } userId
   */
  async viewCountTotal (userId) {
    try {
      const sql = `SELECT SUM(viewCounts) AS total FROM ${this.tableName} WHERE userId = ?`

      const total = await db.query(sql, [userId])

      return total[0].total
    } catch (error) {
      throw new Error(error)
    }
  }
}

module.exports = new ArticleModel()
